package com.charlie.db;

import com.charlie.beans.Groups;
import com.charlie.beans.Product;
import com.charlie.beans.Users;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.sql.DataSource;
import oracle.jdbc.*;
import oracle.sql.DATE;

/**
 *
 * @author Gleb
 */
@Stateless
public class DAO implements DAOLocal {

    @Resource(lookup = "jdbc/charlie_shop_source")
    private DataSource dataSource;
    private Connection conn;

    @PostConstruct
    public void init() {
        try {
            conn = dataSource.getConnection();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @PreDestroy
    public void destroy() {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, "Cannot release connection", ex);
        }
    }

    private void close(PreparedStatement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception ex) {
                Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    public List<String> getProductsIdNameCost() {
        String query = "SELECT PRODUCT_ID, NAME, COST FROM PRODUCT WHERE STATUS = '1'";
        return getList(query);
    }

    @Override
    public List<Product> getProductRange(int from, int to) {
        List<Product> list = new ArrayList<Product>();
        PreparedStatement statement = null;
        String query = "SELECT PRODUCT_ID, \"NAME\", CODE, DESCRIPTION, IMAGE, COST "
                + "FROM ("
                + "SELECT b.*, ROWNUM b_rownum FROM ("
                + "SELECT * FROM PRODUCT WHERE STATUS = '1' ORDER BY PRODUCT_ID) b "
                + "WHERE ROWNUM <= ?) "
                + "WHERE b_rownum > ?";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, to);
            statement.setInt(2, from);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                Product product = new Product(rs.getInt(1));
                product.setName(rs.getString(2));
                product.setCode(rs.getString(3));
                product.setDescription(rs.getString(4));
                product.setImage(rs.getString(5));
                product.setCost(rs.getInt(6));
                list.add(product);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return list;
    }

    @Override
    public List<Users> getUsersRange(int from, int to) {
        PreparedStatement statement = null;
        List<Users> list = new ArrayList<Users>();
        String query = "SELECT USERS_ID, CREATION_DATE, EMAIL, FIRST_NAME, SECOND_NAME "
                + "FROM ("
                + "SELECT b.*, ROWNUM b_rownum FROM ("
                + "SELECT * FROM USERS WHERE USER_STATUS = '1' ORDER BY USERS_ID) b "
                + "WHERE ROWNUM <= " + to + ") "
                + "WHERE b_rownum > " + from;
        try {
            statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                Users user = new Users(rs.getInt(1));
                user.setCreationDate(rs.getDate(2));
                user.setEmail(rs.getString(3));
                user.setFirstName(rs.getString(4));
                user.setSecondName(rs.getString(5));
                list.add(user);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return list;
    }

    /**
     *
     * @param from
     * @param to
     * @return
     */
    @Override
    public List<Groups> getGroupsRange(int from, int to) {
        PreparedStatement statement = null;
        List<Groups> list = new ArrayList<Groups>();
        String query = "SELECT GROUPS_ID, \"NAME\""
                + "FROM ("
                + "SELECT b.*, ROWNUM b_rownum FROM ("
                + "SELECT * FROM GROUPS ORDER BY GROUPS_ID) b "
                + "WHERE ROWNUM <= ?) "
                + "WHERE b_rownum > ?";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, to);
            statement.setInt(2, from);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                Groups group = new Groups(rs.getInt(1));
                group.setName(rs.getString(2));
                list.add(group);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return list;
    }

    @Override
    public List<TopProduct> getProductNameSummCost(int from, int to) {
        List<TopProduct> list = new ArrayList<TopProduct>();
        String query = "SELECT NAME, SUMM, COST FROM ("
                + "SELECT a.*, ROWNUM rnum FROM ("
                + "     SELECT NAME, COST, SUM(AMOUNT) AS SUMM"
                + "     FROM PRODUCT LEFT JOIN SO_PRODUCT"
                + "     USING (PRODUCT_ID)"
                + "     GROUP BY PRODUCT_ID, NAME, COST"
                + "     ORDER BY SUMM DESC"
                + ") a WHERE ROWNUM <= " + to
                + ") WHERE rnum >" + from;
        try {
            ResultSet rs = getDataFromCursor(query);
            if (rs != null) {
                while (rs.next()) {
                    TopProduct order = new TopProduct(rs.getString(1), rs.getInt(2), rs.getInt(3));
                    list.add(order);
                }
                rs.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    @Override
    public List<String> getAllRoles() {
        String query = "SELECT ROLES_ID, ROLES_NAME FROM ROLES ";
        return getList(query);
    }

    @Override
    public List<String> getAllUsers() {
        String query = "SELECT USERS_ID, CREATION_DATE, EMAIL, FIRST_NAME, SECOND_NAME, USER_STATUS FROM USERS ";
        return getList(query);
    }

    @Override
    public List<String> getAllGroups() {
        String query = "SELECT GROUPS_ID, NAME FROM GROUPS ORDER BY GROUPS_ID";
        return getList(query);
    }

    @Override
    public String getGroupRolesNames(int groupID) {
        PreparedStatement statement = null;
        String query = "SELECT ROLES_NAME "
                + "FROM (ROLES INNER JOIN GROUPS_ROLES "
                + "ON ROLES.ROLES_ID = GROUPS_ROLES.ROLES_ID)"
                + "WHERE GROUPS_ROLES.GROUPS_ID = ?";
        StringBuilder sb = new StringBuilder();
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, groupID);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                sb.append(rs.getString(1));
                sb.append(" ");
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return sb.toString();
    }

    @Override
    public List<String> getRolesNotForGroup(String groupID) {
        String query = "SELECT ROLES.ROLES_ID, ROLES_NAME "
                + "FROM \"ROLES\" "
                + "WHERE ROLES_ID NOT IN (SELECT ROLES_ID FROM GROUPS_ROLES WHERE GROUPS_ID ='" + groupID + "')";
        return getList(query);
    }

    private String getQueryFilter(boolean isEmail, boolean[] statuses, long dateFrom, long dateTo) {
        //  prepare email filter
        String queryEmail = "";
        if (isEmail) {
            queryEmail = "(EMAIL = ?)";
        }

        //  prepare date filter
        boolean isDate = false;
        String queryDate = null;
        if ((dateFrom > 0) && (dateTo > 0) && (dateTo > dateFrom)) {
            isDate = true;
            DateFormat formater = new SimpleDateFormat("dd-MMM-yyyy");
            queryDate = "(CREATION_DATE BETWEEN \'" + formater.format(new Date(dateFrom)) + "\' AND \'" + formater.format(new Date(dateTo)) + "\')";
        }
        
        //  prepare status filter
        boolean isStatuses = false;
        String queryStatuses = null;
        List<Integer> checkedStatuses = new ArrayList<Integer>();
        if ((statuses != null) && (statuses.length == 4)) {
            for (int i = 0; i < statuses.length; i++) {
                if (statuses[i]) {
                    checkedStatuses.add(new Integer(i + 1));
                }
            }
        }
        
        if (!checkedStatuses.isEmpty()) {
            isStatuses = true;
            int size = checkedStatuses.size();
            StringBuilder builder = new StringBuilder("(STATUS_DICT_ID IN (");
            builder.append(checkedStatuses.get(0));
            for (int i = 1; i < size; i++) {
                builder.append(", ");
                builder.append(checkedStatuses.get(i));
            }
            builder.append("))");
            queryStatuses = builder.toString();
        }

        //  prepare all filters together
        String queryFilter = "";
        if (isEmail || isStatuses || isDate) {
            boolean isAndNeed = false;
            StringBuilder builder = new StringBuilder();
            builder.append(" WHERE ");

            if (isEmail) {
                isAndNeed = true;
                builder.append(queryEmail);
            }
            
            if (isStatuses) {
                if (isAndNeed) {
                    builder.append(" AND ");
                }
                isAndNeed = true;
                builder.append(queryStatuses);
            }
            
            if (isDate) {
                if (isAndNeed) {
                    builder.append(" AND ");
                }
                builder.append(queryDate);
            }
            
            queryFilter = builder.toString();
        }

        return queryFilter;
    }

    @Override
    public List<ShipmentOrderData> getOrdersByFilter(String email, boolean[] statuses, int from, int to, long dateFrom, long dateTo) {
        List<ShipmentOrderData> list = new ArrayList<ShipmentOrderData>();
        PreparedStatement statement = null;

        //  prepare isEmail
        boolean isEmail;
        if ((email == null) || (email.isEmpty())) {
            isEmail = false;
        } else {
            isEmail = true;
        }
 
        String query = "SELECT SO_ID, CREATION_DATE, DUE_DATE, SHIPING_COST, COST, EMAIL, LOCATION, WAREHOUSE_ID, IS_SUBSCRIBED, STATUS_NAME, STATUS_DICT_ID, NAME FROM "
                + "(SELECT ROWNUM AS rnum, res.* FROM "
                + "  (SELECT * FROM "
                + "    (SELECT a.*, s.STATUS_NAME, CHANGE_DATE, MAX(h.CHANGE_DATE) OVER (partition by a.SO_ID) LAST_CHANGE_DATE, NVL(h.STATUS_DICT_ID, 1) AS STATUS_DICT_ID, w.NAME FROM SHIPMENT_ORDER a"
                + "      LEFT JOIN SO_HISTORY h ON (a.SO_ID = h.SO_ID) "
                + "      LEFT JOIN STATUS_DICT s ON (NVL(h.STATUS_DICT_ID, 1) = s.STATUS_DICT_ID) "
                + "      LEFT JOIN WAREHOUSE w ON (a.WAREHOUSE_ID = w.WAREHOUSE_ID)"
                + "    ) WHERE CHANGE_DATE = LAST_CHANGE_DATE OR CHANGE_DATE IS NULL "
                + "  ) res "
                + getQueryFilter(isEmail, statuses, dateFrom, dateTo)
                + ")"
                + (((from == -1) && (to == -1)) ? "" : " WHERE (rnum BETWEEN " + from + " AND " + to + ")");
        try {
            statement = conn.prepareStatement(query);
            if (isEmail) {
                statement.setString(1, email);
            }
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                ShipmentOrderData order = new ShipmentOrderData(rs.getInt(1));
                order.setCreationDate(rs.getDate(2));
                order.setDueDate(rs.getDate(3));
                order.setShipingCost(rs.getInt(4));
                order.setCost(rs.getInt(5));
                order.setEmail(rs.getString(6));
                order.setLocation(rs.getString(7));
                order.setIsSubscribed(rs.getShort(9));
                order.setStatus(rs.getString(10));
                order.setStatusId(rs.getInt(11));
                order.setWarehouseName(rs.getString(12));
                list.add(order);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, query, ex);
        } finally {
            close(statement);
        }
        return list;
    }

    @Override
    public List<ShipmentOrderData> getOrdersByEmail(String email, int from, int to) {
        return getOrdersByFilter(email, null, from, to, -1, -1);
    }

    @Override
    public List<String> getUserPersonalRolesByEmail(String email) {
        List<String> result = null;
        PreparedStatement statement = null;
        String query = "SELECT roles_name FROM users u "
                + "INNER JOIN users_role ur ON u.users_id = ur.users_id "
                + "INNER JOIN roles r ON ur.roles_id = r.roles_id "
                + "WHERE u.email = ?";
        try {
            statement = conn.prepareStatement(query);
            statement.setString(1, email);
            ResultSet rs = statement.executeQuery();
            result = getSingleColumnResult(rs);
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return result;
    }

    @Override
    public List<String> getUserGroupRolesByEmail(String email) {
        List<String> result = null;
        PreparedStatement statement = null;
        String query = "SELECT roles_name FROM users u "
                + "INNER JOIN users_groups gu ON u.users_id = gu.users_id "
                + "INNER JOIN groups g ON g.groups_id = gu.groups_id "
                + "INNER JOIN groups_roles gr ON gr.groups_id = g.groups_id "
                + "INNER JOIN roles r ON gr.roles_id = r.roles_id "
                + "WHERE u.email = ?";
        try {
            statement = conn.prepareStatement(query);
            statement.setString(1, email);
            ResultSet rs = statement.executeQuery();
            result = getSingleColumnResult(rs);
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return result;
    }

    @Override
    public List<String> getGroupRolesNamesList(int groupID) {
        List<String> result = null;
        PreparedStatement statement = null;
        String query = "SELECT ROLES_NAME "
                + "FROM (ROLES INNER JOIN GROUPS_ROLES "
                + "ON ROLES.ROLES_ID = GROUPS_ROLES.ROLES_ID)"
                + "WHERE GROUPS_ROLES.GROUPS_ID = ?";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, groupID);
            ResultSet rs = statement.executeQuery();
            result = getSingleColumnResult(rs);
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return result;
    }

    private List<String> getSingleColumnResult(ResultSet rs) {
        List<String> result = new ArrayList();
        try {
            while (rs.next()) {
                result.add(rs.getString(1));
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public ResultSet getDataFromCursor(String query) {
        ResultSet rs = null;
        CallableStatement cs;
        try {
            cs = conn.prepareCall("BEGIN PAC_CURSOR.GET_REF_CURSOR ( ?, ? ); END;"); //Calling Oracle procedure
            //Defining type of return
            cs.setString(1, query);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.execute();  //Running the call                
            rs = (ResultSet) cs.getObject(2); //Retrieving the cursor as ResultSet
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }

    public List<String> createListFromResultSet(ResultSet rs) {
        List<String> resultList = new ArrayList();
        if (rs != null) {
            try {
                int columns = rs.getMetaData().getColumnCount();
                StringBuilder sb = new StringBuilder();
                while (rs.next()) {
                    for (int col = 1; col <= columns; col++) {
                        sb.append(rs.getString(col));
                        sb.append(" ");
                    }
                    rs.close();
                    resultList.add(sb.toString());
                    sb = new StringBuilder();
                }
            } catch (SQLException ex) {
                Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return resultList;
    }

    public List<String> getList(String query) {
        ResultSet rs = getDataFromCursor(query);
        return createListFromResultSet(rs);
    }

    @Override
    public int getUsersRoleId(int usersID, int roleID) {
        int result = 0;
        PreparedStatement statement = null;
        String query = "SELECT USERS_ROLE_ID FROM USERS_ROLE WHERE (USERS_ID = ?) AND (ROLES_ID = ?)";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, usersID);
            statement.setInt(2, roleID);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return result;

    }

    @Override
    public void deleteUsersRole(int usersID, int roleID) {
        PreparedStatement statement = null;
        String query = "DELETE FROM USERS_ROLE WHERE (USERS_ID = ?) AND (ROLES_ID = ?)";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, usersID);
            statement.setInt(2, roleID);
            statement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
    }

    @Override
    public void deleteGroupsRole(int groupsID, int roleID) {
        PreparedStatement statement = null;
        String query = "DELETE FROM GROUPS_ROLES WHERE (GROUPS_ID = ?) AND (ROLES_ID = ?)";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, groupsID);
            statement.setInt(2, roleID);
            statement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
    }

    @Override
    public List<String> getUserIdByGroupId(int groupID) {
        List<String> result = null;
        PreparedStatement statement = null;
        String query = "SELECT USERS_ID FROM USERS_GROUPS WHERE GROUPS_ID = ?";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, groupID);
            ResultSet rs = statement.executeQuery();
            result = getSingleColumnResult(rs);
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return result;
    }

    @Override
    public void deleteUsersGroups(int groupsID, int usersID) {
        PreparedStatement statement = null;
        String query = "DELETE FROM USERS_GROUPS WHERE (GROUPS_ID = ?) AND (users_ID = ?)";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, groupsID);
            statement.setInt(2, usersID);
            statement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
    }

    @Override
    public void deleteUsersGroups(int groupsID) {
        PreparedStatement statement = null;
        String query = "DELETE FROM USERS_GROUPS WHERE (GROUPS_ID = ?)";
        try {
            statement = conn.prepareStatement(query);
            statement.setInt(1, groupsID);
            statement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
    }

    @Override
    public FieldDescription getFildsDescription(String table, String field) {
        PreparedStatement statement = null;
        String query = "SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION "
                + "FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
        try {
            statement = conn.prepareStatement(query);
            statement.setString(1, table);
            statement.setString(2, field);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                FieldDescription desc = new FieldDescription(rs.getString(1),
                        rs.getString(2), rs.getInt(3), rs.getInt(4));
                return desc;
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception ex) {
                    Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return null;
    }
    
    @Override
    public String getHash(String email) {
        PreparedStatement statement = null;
        String result = "";
        String query = "SELECT ORA_HASH(CONCAT(EMAIL,CREATION_DATE)) FROM USERS WHERE (EMAIL = ?)";
        try {
            statement = conn.prepareStatement(query);
            statement.setString(1, email);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                result = rs.getString(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return result;
    }
    
    @Override
    public int getUserOrdersCount(String email) {
        int result = 0;
        PreparedStatement statement = null;
        String query = "SELECT COUNT(*) FROM SHIPMENT_ORDER WHERE (EMAIL = ?)";
        try {
            statement = conn.prepareStatement(query);
            statement.setString(1, email);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close(statement);
        }
        return result;
    }
}
